package com.material.common.utils.poi;

import com.material.common.utils.DateUtils;
import com.material.common.utils.StringUtils;
import com.material.pound.params.FuelPoundImportParams;
import com.material.pound.vo.FuelAssayResultVo;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author ganxinge
 */
public class ExcelImportUtil {

    public static List<FuelPoundImportParams> importExcel(InputStream is){
        List<FuelPoundImportParams> dataList = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(is);
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            Row header = sheet.getRow(0);
            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    continue; // 跳过表头
                }
                short lastCellNum = row.getLastCellNum();
                FuelPoundImportParams params = new FuelPoundImportParams();
                List<FuelAssayResultVo> properties = params.getProperties();
                for (int i = 0; i < lastCellNum; i++) {
                    Cell cell = row.getCell(i);
                    String s = getString(cell);
                    if(StringUtils.isBlank(s)){
                        continue;
                    }
                    if(i == 0){
                        params.setReservationDate(DateUtils.parseDate(s));
                    }else if(i==1){
                        params.setSupplierName(s);
                    }else if(i==2){
                        params.setFuelName(s);
                    }else if(i==3){
                        params.setCarNum(s);
                    }else{
                        String prop = getHeadColumn(header,i);
                        FuelAssayResultVo res = new FuelAssayResultVo();
                        res.setAttrName(prop);
                        res.setValue(s);
                        properties.add(res);
                    }
                }
                dataList.add(params);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            IOUtils.closeQuietly(workbook);
        }
        return dataList;
    }

    private static String getHeadColumn(Row header,int index){
        Cell cell = header.getCell(index);
        String string = getString(cell);
        return string;
    }

    private static String getString(Cell cell){
        String str = "";
        switch (cell.getCellType()) {
            case STRING:
                str = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    str = (DateUtils.parseDateToStr("yyyy-MM-dd",date));
                } else {
                    str = (String.valueOf(cell.getNumericCellValue()));
                }
                break;
            case BOOLEAN:
                str = (String.valueOf(cell.getBooleanCellValue()));
                break;
            case FORMULA:
                str = (cell.getCellFormula());
                break;
            case BLANK:
                str = null;
                break;
            case ERROR:
                str = null;
                break;
            default:
                str = null;
        }
        return str;
    }

}
